﻿CREATE PROCEDURE [dbo].[utl_VerifySQLUtility2005]

AS
BEGIN

SET NOCOUNT ON

DECLARE @ID INT
DECLARE @TC_Object sysname
DECLARE @TC_Type sysname
DECLARE @RC INT

DECLARE @True  BIT; SET @True  = 1
DECLARE @False BIT; SET @False = 0

DECLARE @Objects TABLE (
    ID INT IDENTITY (1,1) PRIMARY KEY,
    ObjectName sysname)
    
DECLARE @Results TABLE (
    ID INT IDENTITY (1,1) PRIMARY KEY,
    Found BIT NOT NULL,
    Msg VARCHAR(MAX),
    Indent INT DEFAULT 2)


--------------------------------------------------------------------------------
--------------------------------------------------------------------------------
--  Section: check for database objects                                       --
--------------------------------------------------------------------------------
--------------------------------------------------------------------------------

INSERT INTO @Results (Found, Msg, Indent) SELECT @True, 'SECTION: OBJECTS', 0

--------------------------------------------------------------------------------
--  check for stored procedures                                               --
--------------------------------------------------------------------------------

INSERT INTO @Results (Found, Msg, Indent) SELECT @True, 'STORED PROCEDURES:', 1

DELETE FROM @Objects
INSERT INTO @Objects (ObjectName) VALUES ('utl_AddHousekeeping')
INSERT INTO @Objects (ObjectName) VALUES ('utl_AddHousekeepingToAllTables')
INSERT INTO @Objects (ObjectName) VALUES ('utl_AddTimestamp')
INSERT INTO @Objects (ObjectName) VALUES ('utl_AddTimestampToAllTables')
INSERT INTO @Objects (ObjectName) VALUES ('utl_AssignCRUDExecPermissions')
INSERT INTO @Objects (ObjectName) VALUES ('utl_AssignExecPermissions')
INSERT INTO @Objects (ObjectName) VALUES ('utl_AssignExecToSP')
INSERT INTO @Objects (ObjectName) VALUES ('utl_AssignPermissions')
INSERT INTO @Objects (ObjectName) VALUES ('utl_AssignRoles')
INSERT INTO @Objects (ObjectName) VALUES ('utl_CreateAuditTable')
INSERT INTO @Objects (ObjectName) VALUES ('utl_CreateAuditTrigger')
INSERT INTO @Objects (ObjectName) VALUES ('utl_CreateAuditTriggerForAllTables')
INSERT INTO @Objects (ObjectName) VALUES ('utl_CreateMessageLogTable')
INSERT INTO @Objects (ObjectName) VALUES ('utl_CreateSPDel')
INSERT INTO @Objects (ObjectName) VALUES ('utl_CreateSPForAllTables')
INSERT INTO @Objects (ObjectName) VALUES ('utl_CreateSPForTable')
INSERT INTO @Objects (ObjectName) VALUES ('utl_CreateSPIns')
INSERT INTO @Objects (ObjectName) VALUES ('utl_CreateSPScriptTable')
INSERT INTO @Objects (ObjectName) VALUES ('utl_CreateSPSel')
INSERT INTO @Objects (ObjectName) VALUES ('utl_CreateSPSelAll')
INSERT INTO @Objects (ObjectName) VALUES ('utl_CreateSPSelBy')
INSERT INTO @Objects (ObjectName) VALUES ('utl_CreateSPUpd')
INSERT INTO @Objects (ObjectName) VALUES ('utl_CreateSPView')
INSERT INTO @Objects (ObjectName) VALUES ('utl_IsJobRunning')
INSERT INTO @Objects (ObjectName) VALUES ('utl_ListCRUDProcs')
INSERT INTO @Objects (ObjectName) VALUES ('utl_ListStoredProcsUsingTable')
INSERT INTO @Objects (ObjectName) VALUES ('utl_LogMsg')
INSERT INTO @Objects (ObjectName) VALUES ('utl_PruneAuditTable')
INSERT INTO @Objects (ObjectName) VALUES ('utl_PruneMessageLog')
INSERT INTO @Objects (ObjectName) VALUES ('utl_PruneTable')
INSERT INTO @Objects (ObjectName) VALUES ('utl_RemoveAuditing')
INSERT INTO @Objects (ObjectName) VALUES ('utl_RemoveHousekeeping')
INSERT INTO @Objects (ObjectName) VALUES ('utl_RemoveHousekeepingFromAllTables')
INSERT INTO @Objects (ObjectName) VALUES ('utl_RemoveTimestamp')
INSERT INTO @Objects (ObjectName) VALUES ('utl_RemoveTimestampFromAllTables')
INSERT INTO @Objects (ObjectName) VALUES ('utl_VerifySQLUtility2005')

SELECT @ID = MIN(ID) FROM @Objects
WHILE @ID IS NOT NULL
BEGIN
    SELECT @TC_Object = ObjectName FROM @Objects WHERE ID = @ID
    IF (OBJECT_ID(@TC_Object) IS NOT NULL)
        INSERT @Results (Found, Msg) SELECT @True, 'Stored Procedure ''' + @TC_Object + ''' found.'
    ELSE
        INSERT @Results (Found, Msg) SELECT @False, 'Stored Procedure ''' + @TC_Object + ''' not found.'
    SELECT @ID = MIN(ID) FROM @Objects WHERE ID > @ID
END


--------------------------------------------------------------------------------
--  check for functions                                                       --
--------------------------------------------------------------------------------

INSERT INTO @Results (Found, Msg, Indent) SELECT @True, 'FUNCTIONS:', 1

DELETE FROM @Objects
INSERT INTO @Objects (ObjectName) VALUES ('fn_ColumnDatatype')
INSERT INTO @Objects (ObjectName) VALUES ('fn_ColumnDefaultConstraint')
INSERT INTO @Objects (ObjectName) VALUES ('fn_ColumnExists')
INSERT INTO @Objects (ObjectName) VALUES ('fn_ColumnIsComputed')
INSERT INTO @Objects (ObjectName) VALUES ('fn_ColumnIsIdentity')
INSERT INTO @Objects (ObjectName) VALUES ('fn_ColumnIsNullable')
INSERT INTO @Objects (ObjectName) VALUES ('fn_ColumnIsPartOfPrimaryKey')
INSERT INTO @Objects (ObjectName) VALUES ('fn_ColumnIsTimestamp')
INSERT INTO @Objects (ObjectName) VALUES ('fn_ColumnList')
INSERT INTO @Objects (ObjectName) VALUES ('fn_ColumnListAll')
INSERT INTO @Objects (ObjectName) VALUES ('fn_ColumnMaxLength')
INSERT INTO @Objects (ObjectName) VALUES ('fn_Columns')
INSERT INTO @Objects (ObjectName) VALUES ('fn_ColumnsAll')
INSERT INTO @Objects (ObjectName) VALUES ('fn_ColumnsAndAttributes')
INSERT INTO @Objects (ObjectName) VALUES ('fn_ColumnsAndAttributesAll')
INSERT INTO @Objects (ObjectName) VALUES ('fn_CRUDProcs')
INSERT INTO @Objects (ObjectName) VALUES ('fn_CustomProcs')
INSERT INTO @Objects (ObjectName) VALUES ('fn_FunctionExists')
INSERT INTO @Objects (ObjectName) VALUES ('fn_HousekeepingColumnList')
INSERT INTO @Objects (ObjectName) VALUES ('fn_HousekeepingColumns')
INSERT INTO @Objects (ObjectName) VALUES ('fn_HousekeepingFromClause')
INSERT INTO @Objects (ObjectName) VALUES ('fn_IsFourPartName')
INSERT INTO @Objects (ObjectName) VALUES ('fn_NumericPrecision')
INSERT INTO @Objects (ObjectName) VALUES ('fn_NumericScale')
INSERT INTO @Objects (ObjectName) VALUES ('fn_ObjectType')
INSERT INTO @Objects (ObjectName) VALUES ('fn_PrimaryKeyColumnList')
INSERT INTO @Objects (ObjectName) VALUES ('fn_PrimaryKeyColumns')
INSERT INTO @Objects (ObjectName) VALUES ('fn_PrimaryKeyConstraintName')
INSERT INTO @Objects (ObjectName) VALUES ('fn_ProcedureExists')
INSERT INTO @Objects (ObjectName) VALUES ('fn_QuoteName')
INSERT INTO @Objects (ObjectName) VALUES ('fn_RoutineExists')
INSERT INTO @Objects (ObjectName) VALUES ('fn_SPScriptSeparator')
INSERT INTO @Objects (ObjectName) VALUES ('fn_TableExists')
INSERT INTO @Objects (ObjectName) VALUES ('fn_TableHasIdentityColumn')
INSERT INTO @Objects (ObjectName) VALUES ('fn_TableHasPrimaryKey')
INSERT INTO @Objects (ObjectName) VALUES ('fn_TableHasTimestamp')
INSERT INTO @Objects (ObjectName) VALUES ('fn_Tables')
INSERT INTO @Objects (ObjectName) VALUES ('fn_TablesAll')
INSERT INTO @Objects (ObjectName) VALUES ('fn_TablesToIgnore')
INSERT INTO @Objects (ObjectName) VALUES ('fn_TimestampColumn')
INSERT INTO @Objects (ObjectName) VALUES ('fn_TimestampColumns')
INSERT INTO @Objects (ObjectName) VALUES ('fn_TriggerExists')
INSERT INTO @Objects (ObjectName) VALUES ('fn_UserExists')
INSERT INTO @Objects (ObjectName) VALUES ('fn_UtilProcs')
INSERT INTO @Objects (ObjectName) VALUES ('fn_ViewExists')

SELECT @ID = MIN(ID) FROM @Objects
WHILE @ID IS NOT NULL
BEGIN
    SELECT @TC_Object = ObjectName FROM @Objects WHERE ID = @ID
    IF (OBJECT_ID(@TC_Object) IS NOT NULL)
        INSERT @Results (Found, Msg) SELECT @True, 'Function ''' + @TC_Object + ''' found.'
    ELSE
        INSERT @Results (Found, Msg) SELECT @False, 'Function ''' + @TC_Object + ''' not found.'
    SELECT @ID = MIN(ID) FROM @Objects WHERE ID > @ID
END


--------------------------------------------------------------------------------
--------------------------------------------------------------------------------

SELECT CASE WHEN Found = @True THEN '' WHEN Found IS NULL THEN '' ELSE 'X' END AS 'MISSING', 
       SPACE(ISNULL(Indent,0)*5) + Msg AS MESSAGE 
    FROM @Results ORDER BY ID

SET NOCOUNT OFF

END

